package edu.iyte.ceng431.hw2.pos.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.iyte.ceng431.hw2.pos.model.Cashier;
import edu.iyte.ceng431.hw2.pos.model.Product;
import edu.iyte.ceng431.hw2.pos.model.Register;
import edu.iyte.ceng431.hw2.pos.model.Sale;
import edu.iyte.ceng431.hw2.pos.model.SaleLineItem;

public class Search {
	
	public static List<Integer> getAllRegisters()
	{
		List<Integer> registers = new ArrayList<Integer>();
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Register");
			while (rset.next()) {
				int id = rset.getInt("id");
				registers.add(id);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return registers;
	}
	
	public static List<Cashier> getAllCashiers()
	{
		List<Cashier> cashiers = new ArrayList<Cashier>();
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Cashier");
			while (rset.next()) {
				int id = rset.getInt("id");
				String name = rset.getString("name");
				cashiers.add(new Cashier(id, name));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cashiers;
	}

	/*public static List<Product> getAllProducts() {
		List<Product> products = new ArrayList<Product>();
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Product");
			while (rset.next()) {
				int id = rset.getInt("id");
				String name = rset.getString("name");
				int price = rset.getInt("price");
				products.add(new Product(id, name, price));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return products;
	}*/
	
	public static Sale getSale(int saleId)
	{
		Sale sale = null;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Sale where id = "
					+ saleId);
			if (!rset.next())
				return null;
			Date date = rset.getDate("date");
			sale = new Sale(saleId, date );
			rset = statement.executeQuery("select * from SaleLine where saleId = "
					+ saleId);
			while(rset.next())
			{
				int lineId = rset.getInt("id");
				int quantity = rset.getInt("quantity");
				Product product = getProduct(rset.getInt("productId"));
				sale.addSaleLineItem(lineId,product, quantity);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sale;
	}
	
	public static SaleLineItem getSaleLineItem(int lineId)
	{
		SaleLineItem saleLine = null;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from SaleLine,Product where id = "
					+ lineId);
			if (!rset.next())
				return null;
			int quantity = rset.getInt("quantity");
			Product product = getProduct(rset.getInt("productId"));
			saleLine = new SaleLineItem(lineId,product, quantity);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return saleLine;
	}
	
	public static Product getProduct(int productId)
	{
		Product product = null;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Product where id = "
					+ productId);
			if (!rset.next())
				return null;
			String name = rset.getString("name");
			double price = rset.getDouble("price");
			product = new Product(productId, name, price);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}
	
	public static Cashier getCashier(int cashierId)
	{
		Cashier cashier = null;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Cashier where id = "
					+ cashierId);
			if (!rset.next())
				return null;
			String name = rset.getString("name");
			cashier = new Cashier(cashierId, name);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cashier;
	}
	
	public static Register getRegister(int registerId,double balance,Cashier cas)
	{
		Register register;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Register where id = "
					+ registerId);
			if (!rset.next())
				return null;
			register = new Register(rset.getInt("id"), balance, cas);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		return register;
		
	}

	public static double getTotalSale(Date date1, Date date2,int formOfPayment) {
		Connection connection = ConnectDB.getConnection();
		double total = 0;
		
		java.sql.Timestamp timeStamp1 = new java.sql.Timestamp(date1.getTime());
		java.sql.Timestamp timeStamp2 = new java.sql.Timestamp(date2.getTime());
		String query = "select SUM(Payment.amount) from Payment,Sale where Payment.form = ? " +
				"AND Payment.saleId = Sale.id AND Sale.date between ? and ?";

		try {
			PreparedStatement pStatement = connection.prepareStatement(query);
			pStatement.setInt(1, formOfPayment);
			pStatement.setTimestamp(2, timeStamp1);
			pStatement.setTimestamp(3, timeStamp2);
			ResultSet rset = pStatement.executeQuery();
			if(!rset.next()) return 0;
			total = rset.getDouble(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	public static double getTotalCash(Date date1, Date date2,
			Register register) {
		Connection connection = ConnectDB.getConnection();
		double total = 0;
		
		java.sql.Timestamp timeStamp1 = new java.sql.Timestamp(date1.getTime());
		java.sql.Timestamp timeStamp2 = new java.sql.Timestamp(date2.getTime());
		String query = "select SUM(Payment.amount) from Payment,Sale where Sale.registerId = ? " +
				"AND Payment.saleId = Sale.id AND Sale.date between ? and ? AND Payment.form = 1";

		try {
			PreparedStatement pStatement = connection.prepareStatement(query);
			pStatement.setInt(1, register.getId());
			pStatement.setTimestamp(2, timeStamp1);
			pStatement.setTimestamp(3, timeStamp2);
			ResultSet rset = pStatement.executeQuery();
			if(!rset.next()) return 0;
			total = rset.getDouble(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	

	public static int getMaxSaleId() {
		int max = 0;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select MAX(id) from Sale");
			if (!rset.next())
				return 1;
			max = rset.getInt("MAX(id)");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return max + 1;
	}
	
	public static int getMaxSaleLineId() {
		int max = 0;
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement
					.executeQuery("select MAX(id) from SaleLine");
			if (!rset.next())
				return 1;
			max = rset.getInt("MAX(id)");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return max + 1;
	}

	/*public static List<Sale> getAllSales(Register reg, Cashier cas) {
		List<Sale> sales = new ArrayList<Sale>();
		Connection connection = ConnectDB.getConnection();
		try {
			Statement statement = connection.createStatement();
			ResultSet rset = statement.executeQuery("select * from Sale where registerId = "
					+ reg.getId() + " AND cashierId =" + cas.getId());
			while (rset.next()) {
				int id = rset.getInt("id");
				Sale sale = new Sale(id, reg, cas);
				ResultSet rsetLines = statement.executeQuery("select * from SaleLine,Product where saleId = "
						+ id);
				while(rsetLines.next())
				{
					int lineId = rsetLines.getInt("SaleLine.id");
					Product product = 
					SaleLineItem line = new SaleLineItem(lineId, product, quantity)
				}
				
				sales.add(sale);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sales;
	}*/

}
